﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Model;

namespace DAL
{
    public class QuestionDAL
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetAllQuestion()
        {
            return DbHelperSQL.Query("select * from ExamDB.dbo.tb_Question");
        }

        public static DataSet GetQuestionByDimension(string dimID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select question_id,question_no,question_title,question_answer_A,
                          question_answer_B,question_answer_C,question_answer_D,question_answer_E,
                          question_answer_A_score,question_answer_B_score,question_answer_C_score,
                          question_answer_D_score,question_answer_E_score,question_dimensionid,
                          question_typeid, CASE question_status WHEN 1 THEN '禁用' ELSE '启用' END AS question_status");
            strSql.Append(" FROM ExamDB.dbo.tb_Question ");
            strSql.Append(@"where question_dimensionid = ");
            strSql.Append(dimID);
            strSql.Append(" order by question_no");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetQuestionById(string questionId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM ExamDB.dbo.tb_Question ");
            strSql.Append(@"where question_id = ");
            strSql.Append(questionId);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetQuestionByGUID(string guid)
        {
            DataSet dsQuestion = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append(@"select activity_id ");
            sb.Append(@"from dbo.tb_GUID_Number where guid_str = ");
            sb.Append(@"'");
            sb.Append(guid);
            sb.Append(@"'");

            DataSet ds = DbHelperSQL.Query(sb.ToString());

            if(ds==null || ds.Tables[0].Rows.Count !=1)
            {
                return null;
            }
            int activityID = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());


            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select dimension_id ");
            strSql.Append(@"from dbo.tb_Activity_Dimension where activity_id = ");
            strSql.Append(activityID.ToString());

            DataSet dsDim = DbHelperSQL.Query(strSql.ToString());

            if (dsDim == null || dsDim.Tables[0].Rows.Count <= 0)
            {
                return null;
            }

            int totalDim = dsDim.Tables[0].Rows.Count;
            int[] dimensions = new int[totalDim];
            int dimID = 0;
            StringBuilder sbb = new StringBuilder();
            DataTable dt = new DataTable();
            //DimensionDAL dimDAL = new DimensionDAL();

            for (int i = 0; i < totalDim; i++)
            {
                sbb.Clear();
                dt.Clear();
                dimID = Int32.Parse(dsDim.Tables[0].Rows[i][0].ToString());
                
                sbb.Append(@"select * from dbo.tb_Question ");
                sbb.Append(@"where question_dimensionid = ");
                sbb.Append(dimID.ToString());

                dt = DbHelperSQL.Query(sbb.ToString()).Tables[0];
               //dt.TableName = DimensionDAL.GetModel(dimID).Dimnm;
                dt.TableName = dimID.ToString();

                dsQuestion.Tables.Add(dt.Copy());
            }

            return dsQuestion;
        }

        public static DataSet GetQuestionByGUIDRand(string guid,out int questionNum)
        {
            DataSet dsQuestion = new DataSet();
            DataSet dsQuestion2 = new DataSet();
            StringBuilder sb = new StringBuilder();
            questionNum = 0;
            sb.Append(@"select activity_id ");
            sb.Append(@"from dbo.tb_GUID_Number where guid_str = ");
            sb.Append(@"'");
            sb.Append(guid);
            sb.Append(@"'");

            DataSet ds = DbHelperSQL.Query(sb.ToString());

            if (ds == null || ds.Tables[0].Rows.Count != 1)
            {
                return null;
            }
            int activityID = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());

            //处理非智力题目。
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from dbo.tb_Question where question_dimensionid in (select tb_Activity_Dimension.dimension_id from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id=tb_Dimension.dimension_id and dimension_class_id!='3' and tb_Activity_Dimension.activity_id =");
            strSql.Append(activityID.ToString());
            strSql.Append(@") order by newid() ");

            dsQuestion = DbHelperSQL.Query(strSql.ToString());
            if (dsQuestion == null || dsQuestion.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            questionNum = dsQuestion.Tables[0].Rows.Count;

            //处理智力题目
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(@"select * from dbo.tb_Question where question_dimensionid in (select tb_Activity_Dimension.dimension_id from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id=tb_Dimension.dimension_id and dimension_class_id ='3' and tb_Activity_Dimension.activity_id =");
            strSql2.Append(activityID.ToString());
            strSql2.Append(@")");
            dsQuestion2 = DbHelperSQL.Query(strSql2.ToString());

            if (dsQuestion2 == null || dsQuestion2.Tables[0].Rows.Count <= 0)
            {
                return dsQuestion;
            }

            //合并结果
            dsQuestion.Merge(dsQuestion2);
            return dsQuestion;
        }

        public static DataSet GetQuestionByActivityIDRand(string actid)
        {
            DataSet dsQuestion = new DataSet();
            DataSet dsQuestion2 = new DataSet();
            //处理非智力题目。
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from dbo.tb_Question where question_dimensionid in (select tb_Activity_Dimension.dimension_id from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id=tb_Dimension.dimension_id and dimension_class_id!='3' and tb_Activity_Dimension.activity_id =");
            strSql.Append(actid);
            strSql.Append(@") order by newid() ");

            dsQuestion = DbHelperSQL.Query(strSql.ToString());
            if (dsQuestion == null || dsQuestion.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            //处理智力题目
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(@"select * from dbo.tb_Question where question_dimensionid in (select tb_Activity_Dimension.dimension_id from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id=tb_Dimension.dimension_id and dimension_class_id ='3' and tb_Activity_Dimension.activity_id =");
            strSql2.Append(actid);
            strSql2.Append(@")");
            dsQuestion2 = DbHelperSQL.Query(strSql2.ToString());

            if (dsQuestion2 == null || dsQuestion2.Tables[0].Rows.Count <= 0)
            {
                return dsQuestion;
            }

            //合并结果
            dsQuestion.Merge(dsQuestion2);
            return dsQuestion;
        }
        /// <summary>
        /// 获取答题估计时间
        /// </summary>
        /// <param name="guid">测评序列号</param>
        /// <param name="nType">1.原始均值 2.最小值 3.最大值</param>
        /// <returns></returns>
        public static int GetQuestionTimeByGUID(string guid,int nType)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select activity_id ");
            sb.Append(@"from dbo.tb_GUID_Number where guid_str = ");
            sb.Append(@"'");
            sb.Append(guid);
            sb.Append(@"'");

            DataSet ds = DbHelperSQL.Query(sb.ToString());

            if (ds == null || ds.Tables[0].Rows.Count != 1)
            {
                return -1;
            }
            int activityID = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
            ds = null;
            //统计答题时间
            StringBuilder strSql = new StringBuilder();

            if (1 == nType)
            {
                strSql.Append(@"select sum (dimension_time_min) from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id
            =dbo.tb_Dimension.dimension_id and tb_Activity_Dimension.activity_id=");
            }
            else if (2 == nType)
            {
                strSql.Append(@"select sum (dimension_time_xiaxian) from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id
            =dbo.tb_Dimension.dimension_id and tb_Activity_Dimension.activity_id=");
            }
            else if (3 == nType)
            {
                strSql.Append(@"select sum (dimension_time_shangxian) from dbo.tb_Activity_Dimension,dbo.tb_Dimension where tb_Activity_Dimension.dimension_id
            =dbo.tb_Dimension.dimension_id and tb_Activity_Dimension.activity_id=");
            }

            strSql.Append(activityID.ToString());

            ds = DbHelperSQL.Query(strSql.ToString());
            if (ds == null || ds.Tables[0].Rows.Count <= 0)
            {
                return -1;
            }
            else
            {
                if (!ds.Tables[0].Rows[0][0].Equals(DBNull.Value))
                {
                    return Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
                }
                else
                {
                    return 0;
                }
            }
        }

        public static int Delete(int QuestionID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete ExamDB.dbo.tb_Question ");
            strSql.Append(" where question_id=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = QuestionID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除题目表中的所有数据
        /// </summary>
        /// <returns>删除的条数</returns>
        public static int DeleteAllQuestion()
        {
            return DbHelperSQL.ExecuteSql("delete dbo.tb_Question");
        }

        /// <summary>
        /// 删除题目表中的题目，按维度
        /// </summary>
        /// <param name="dimID">维度ID</param>
        /// <returns>删除的条数</returns>
        public static int DeleteQuestionByDim(int dimID)
        {
            return DbHelperSQL.ExecuteSql("delete dbo.tb_Question where question_dimensionid = " + dimID.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Question model)
        {
            StringBuilder strSqlNew = new StringBuilder();
            strSqlNew.Append("select count(1) from ExamDB.dbo.tb_Question");
            strSqlNew.Append(" where question_no=@question_no and question_dimensionid=@question_dimensionid");
            SqlParameter[] parametersNew = { new SqlParameter("@question_no", SqlDbType.Int, 4),
                                             new SqlParameter("@question_dimensionid",SqlDbType.Int,4)};

            parametersNew[0].Value = model.Question_no;
            parametersNew[1].Value = model.Question_dimensionid;

            if(DbHelperSQL.Exists(strSqlNew.ToString(), parametersNew))
            {
                return 0;
            }
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ExamDB.dbo.tb_Question(");
            strSql.Append(@"question_no,
                            question_title,
                            question_answer_A,
                            question_answer_B,
                            question_answer_C,
                            question_answer_D,
                            question_answer_E,
                            question_answer_A_score,
                            question_answer_B_score,
                            question_answer_C_score,
                            question_answer_D_score,
                            question_answer_E_score,
                            question_dimensionid,
                            question_typeid,
                            question_status)");

            strSql.Append(" values (");

            strSql.Append(@"@question_no,
                            @question_title,
                            @question_answer_A,
                            @question_answer_B,
                            @question_answer_C,
                            @question_answer_D,
                            @question_answer_E,
                            @question_answer_A_score,
                            @question_answer_B_score,
                            @question_answer_C_score,
                            @question_answer_D_score,
                            @question_answer_E_score,
                            @question_dimensionid,
                            @question_typeid,
                            @question_status)");

            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@question_no", SqlDbType.Int,4),
					new SqlParameter("@question_title", SqlDbType.NVarChar,2255),
					new SqlParameter("@question_answer_A", SqlDbType.NVarChar,2255),
					new SqlParameter("@question_answer_B", SqlDbType.NVarChar,2255),
                    new SqlParameter("@question_answer_C",SqlDbType.NVarChar,2255),
					new SqlParameter("@question_answer_D", SqlDbType.NVarChar,2255),
					new SqlParameter("@question_answer_E", SqlDbType.NVarChar,2255),
                    new SqlParameter("@question_answer_A_score", SqlDbType.Int, 4),
                    new SqlParameter("@question_answer_B_score", SqlDbType.Int, 4),
                    new SqlParameter("@question_answer_C_score", SqlDbType.Int, 4),
                    new SqlParameter("@question_answer_D_score", SqlDbType.Int, 4),
                    new SqlParameter("@question_answer_E_score", SqlDbType.Int, 4),
                    new SqlParameter("@question_dimensionid", SqlDbType.Int, 4),
                    new SqlParameter("@question_typeid", SqlDbType.Int, 4),
                    new SqlParameter("@question_status", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = model.Question_no;
            parameters[1].Value = model.Question_title;
            parameters[2].Value = model.Question_answer_A;
            parameters[3].Value = model.Question_answer_B;
            parameters[4].Value = model.Question_answer_C;
            parameters[5].Value = model.Question_answer_D;
            parameters[6].Value = model.Question_answer_E;
            parameters[7].Value = model.Question_answer_A_score;
            parameters[8].Value = model.Question_answer_B_score;
            parameters[9].Value = model.Question_answer_C_score;
            parameters[10].Value = model.Question_answer_D_score;
            parameters[11].Value = model.Question_answer_E_score;
            parameters[12].Value = model.Question_dimensionid;
            parameters[13].Value = model.Question_typeid;
            parameters[14].Value = model.Question_status;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public static int UpdateQuestionStatus(string questionid, bool isDisable)
        {
            StringBuilder strSql = new StringBuilder();
            if (isDisable)
            {
                strSql.Append("update ExamDB.dbo.tb_Question set question_status=0 ");
                strSql.Append("where question_id='");
                strSql.Append(questionid + "'"); 
            }
            else
            {
                strSql.Append("update ExamDB.dbo.tb_Question set question_status=1 ");
                strSql.Append("where question_id='");
                strSql.Append(questionid + "'"); 
            }
            if (DbHelperSQL.Query(strSql.ToString()) != null)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static int Update(Question model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_Question set ");
            strSql.Append("question_title=@question_title,");
            strSql.Append("question_answer_A=@question_answer_A,");
            strSql.Append("question_answer_B=@question_answer_B,");
            strSql.Append("question_answer_C=@question_answer_C,");
            strSql.Append("question_answer_D=@question_answer_D,");
            strSql.Append("question_answer_E=@question_answer_E,");
            strSql.Append("question_answer_A_score=@question_answer_A_score,");
            strSql.Append("question_answer_B_score=@question_answer_B_score,");
            strSql.Append("question_answer_C_score=@question_answer_C_score,");
            strSql.Append("question_answer_D_score=@question_answer_D_score,");
            strSql.Append("question_answer_E_score=@question_answer_E_score");
            strSql.Append(" where question_id=@question_id");
            SqlParameter[] parameters = {
                    new SqlParameter("@question_title", SqlDbType.NVarChar,255),                                
				    new SqlParameter("@question_answer_A", SqlDbType.NVarChar,255),
					new SqlParameter("@question_answer_B", SqlDbType.NVarChar,255),
					new SqlParameter("@question_answer_C", SqlDbType.NVarChar,255),
                    new SqlParameter("@question_answer_D", SqlDbType.NVarChar,255),
                    new SqlParameter("@question_answer_E", SqlDbType.NVarChar,255),
                    new SqlParameter("@question_answer_A_score", SqlDbType.Int,4),
                    new SqlParameter("@question_answer_B_score", SqlDbType.Int,4),
                    new SqlParameter("@question_answer_C_score", SqlDbType.Int,4),
                    new SqlParameter("@question_answer_D_score", SqlDbType.Int,4),
                    new SqlParameter("@question_answer_E_score", SqlDbType.Int,4),
					new SqlParameter("@question_id", SqlDbType.Int,4)};
            parameters[0].Value = model.Question_title;
            parameters[1].Value = model.Question_answer_A;
            parameters[2].Value = model.Question_answer_B;
            parameters[3].Value = model.Question_answer_C;
            parameters[4].Value = model.Question_answer_D;
            parameters[5].Value = model.Question_answer_E;
            parameters[6].Value = model.Question_answer_A_score;
            parameters[7].Value = model.Question_answer_B_score;
            parameters[8].Value = model.Question_answer_C_score;
            parameters[9].Value = model.Question_answer_D_score;
            parameters[10].Value = model.Question_answer_E_score;
            parameters[11].Value = model.Question_id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

    }
}
